﻿Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Imports Microsoft.Data.Sqlite

Public Class 私人账本
    Public ufee, ufeesort, uremark As String
    Public UID As Integer
    Public Sub QueryAndShowData()
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                If FeeSort.Text = "" Then
                    Dim strsql As String = "SELECT * FROM ACCOUNTBOOK ORDER BY 更新时间 DESC LIMIT 100"
                    Using cmd As New SqliteCommand(strsql, connection)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable
                            End If
                        End Using
                    End Using
                Else
                    Dim strsql As String = "SELECT * FROM ACCOUNTBOOK where 费用分类=@proj ORDER BY 更新时间 DESC LIMIT 100"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@proj", FeeSort.Text)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable
                            End If
                        End Using
                    End Using
                End If
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using

    End Sub
    Private Sub ConfigureGridViewColumns()
        With DataGridView1
            If DataGridView1 IsNot Nothing Then
                .Columns("选择").Width = 40
                .Columns("序号").Width = 40
                .Columns("费用分类").Width = 120
                .Columns("费用").Width = 60
                .Columns("备注").Width = 200
                .Columns("更新时间").Width = 150
                .Columns("序号").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells  ' 包含标题行
            End If
        End With
    End Sub

    Private Sub Label4_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub Query_Click(sender As Object, e As EventArgs) Handles Query.Click
        QueryAndShowData()
        ConfigureGridViewColumns()
    End Sub

    Private Sub AddNew_Click(sender As Object, e As EventArgs) Handles AddNew.Click
        ufee = Trim(Fee.Text)
        ufeesort = Trim(FeeSort.Text)
        If ufee = "" Or ufeesort = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "INSERT INTO accountbook (费用,费用分类,备注,更新时间) VALUES(@ufee, @ufeesort,@remark,@updateTime)"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@ufee", ufee)
                        cmd.Parameters.AddWithValue("@ufeesort", ufeesort)
                        cmd.Parameters.AddWithValue("@remark", Remark.Text)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            QueryAndShowData()
            MsgBox（"新增记录成功"， vbOKOnly, "提示"）
        End If
    End Sub

    Private previousCheckedRow As Integer = -1
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex < 0 OrElse e.ColumnIndex <> 0 Then Exit Sub
        If e.ColumnIndex = 0 AndAlso e.RowIndex >= 0 Then '假设复选框列为第0列
            If previousCheckedRow <> -1 AndAlso previousCheckedRow <> e.RowIndex Then
                DataGridView1.Rows(previousCheckedRow).Cells(0).Value = False
            End If
            DataGridView1.Rows(e.RowIndex).Cells(0).Value = Not DataGridView1.Rows(e.RowIndex).Cells(0).Value
            previousCheckedRow = e.RowIndex
        End If
        Try
            Dim currentRow = DataGridView1.Rows(e.RowIndex)
            UID = currentRow.Cells("序号").Value
            Fee.Text = currentRow.Cells("费用").Value
            FeeSort.Text = currentRow.Cells("费用分类").Value
            Remark.Text = currentRow.Cells("备注").Value
        Catch ex As Exception
            MessageBox.Show($"操作异常：{ex.Message}")
        End Try
    End Sub

    Private Sub feesort_Click(sender As Object, e As EventArgs) Handles FeeSort.Click
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT 分类 FROM dictionary where 项目=@accountbook ORDER BY 序号"
                Using cmd As New SqliteCommand(strsql, connection)
                    cmd.Parameters.AddWithValue("@accountbook", "ActBook")
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        Dim dataTable As New DataTable()
                        dataTable.Load(reader)
                        If dataTable.Rows.Count > 0 Then
                            FeeSort.DataSource = dataTable
                            FeeSort.DisplayMember = "分类"
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub

    Private Sub Modify_Click(sender As Object, e As EventArgs) Handles Modify.Click
        ufee = Trim(Fee.Text)
        ufeesort = Trim(FeeSort.Text)
        uremark = Trim(Remark.Text)

        If ufee = "" Or ufeesort = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "UPDATE accountbook SET 费用 = @fee,费用分类 = @feesort,备注 = @remark,更新时间 = @updateTime WHERE 序号=@uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.Parameters.AddWithValue("@fee", ufee)
                        cmd.Parameters.AddWithValue("@feesort", ufeesort)
                        cmd.Parameters.AddWithValue("@remark", uremark)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            FeeSort.Text = ""
            QueryAndShowData()
            MsgBox（"修改记录成功"， vbOKOnly, "提示"）
        End If
    End Sub

    Private Sub Delete_Click(sender As Object, e As EventArgs) Handles Delete.Click
        Dim result As DialogResult = MessageBox.Show("确定要删除这条记录吗？", "确认删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = DialogResult.Yes Then
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "delete from accountbook WHERE 序号 = @uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            FeeSort.Text = ""
            QueryAndShowData()
        End If
        MsgBox（"删除记录成功"， vbOKOnly, "提示"）
    End Sub

    Private Sub Refresh_Click(sender As Object, e As EventArgs) Handles Refresh.Click
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT 更新时间 FROM ACCOUNTBOOK ORDER BY 更新时间 ASC LIMIT 1"
                Using cmd As New SqliteCommand(strsql, connection)
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            ' 检查是否和原密码一致
                            reader.Read()
                            Starttime.Text = reader.GetString(reader.GetOrdinal("更新时间"))
                        End If
                    End Using
                End Using

            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using

        Try
            ' 1. 检查输入是否为空 
            If String.IsNullOrEmpty(Starttime.Text) Then Exit Sub

            ' 2. 尝试转换为DateTime 
            Dim startDate As DateTime
            If Not DateTime.TryParse(Starttime.Text, startDate) Then
                Recorddays.Text = "时间格式不正确"
                Exit Sub
            End If

            ' 3. 计算时间差
            Dim timeDiff As TimeSpan = Now - startDate

            ' 4. 格式化显示（根据需要选择）
            ' 显示总天数（含小数）
            Recorddays.Text = timeDiff.TotalDays.ToString("0.0")

            ' 或者显示完整时间差
            ' Recorddays.Text = $"{(CInt(timeDiff.TotalDays))}天 {timeDiff.Hours}小时 {timeDiff.Minutes}分钟"

        Catch ex As Exception
            Recorddays.Text = "计算错误: " & ex.Message
        End Try

        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT SUM([费用]) AS [总费用] FROM [ACCOUNTBOOK]"

                Using cmd As New SqliteCommand(strsql, connection)
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            reader.Read()

                            ' 安全获取数值
                            If Not reader.IsDBNull(reader.GetOrdinal("总费用")) Then
                                Dim utotalIncome As Decimal = reader.GetDecimal(reader.GetOrdinal("总费用"))
                                Totalincome.Text = utotalIncome.ToString

                                ' 安全计算日均收入 
                                Dim urecordDays As Double = Recorddays.Text.ToString
                                If Double.TryParse(urecordDays, urecordDays) AndAlso urecordDays > 0 Then
                                    Dailyincome.Text = (utotalIncome / urecordDays).ToString("0.00")
                                Else
                                    Dailyincome.Text = "N/A"

                                End If
                            Else
                                Totalincome.Text = "0.00"
                                Dailyincome.Text = "0.00"
                            End If
                        Else
                            Totalincome.Text = "0.00"
                            Dailyincome.Text = "0.00"
                        End If
                    End Using
                End Using

            Catch ex As SqliteException
                Debug.WriteLine($"数据库异常：{ex.ToString()}")
                MessageBox.Show($"数据库操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Catch ex As Exception
                Debug.WriteLine($"一般异常：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using


    End Sub
End Class